	package com.saic.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.PooledConnection;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;

@SuppressWarnings("deprecation")
public class OracleUtils {
	
	private static  String url ;
	private static  String user ;
	private static  String password ;
	private static  Connection con = null;	
	private static  PreparedStatement statement = null;
	private static  String dbname = null;
	private static  PooledConnection dbpool = null;
	
	
	static {
			InputStream in = OracleUtils.class.getClassLoader().getResourceAsStream("oracle.properties");
			Properties properties = new Properties();
			try {
				properties.load(in);
				url = properties.getProperty("url");
				user = properties.getProperty("user");
				password = properties.getProperty("password");
				Class.forName("oracle.jdbc.driver.OracleDriver");
				String[] urllist = url.split(":");
				dbname = urllist[5];				
				
				OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();
				ocpds.setURL(url);
				ocpds.setUser(user);
				ocpds.setPassword(password);
				ocpds.setMaxStatements(100);
				
				dbpool = ocpds.getPooledConnection();
				
				
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
	}

	public synchronized static  List<Map<String,String>> getDate (String orcl_table,String schemaRow){
		
		List<Map<String,String>> listMap = new ArrayList<Map<String,String>>();
	    try {
	    	con = dbpool.getConnection();
			String sql = "SELECT * FROM "+orcl_table+" where rownum<=100";
			System.out.println(sql);
			statement = con.prepareStatement(sql);
			ResultSet result = statement.executeQuery();
			java.sql.ResultSetMetaData rsmd = result.getMetaData();
			int colNum = rsmd.getColumnCount();
			while(result.next()){
				Map<String,String> dataMap = new HashMap<String,String>(0);
				for(int i = 1;i<=colNum;){
					if(null == result.getObject(i)){
						dataMap.put(rsmd.getColumnName(i),"");
						System.out.println("dataMap.put("+rsmd.getColumnName(i)+",\"\")");
					} else{
						dataMap.put(rsmd.getColumnName(i),result.getObject(i).toString());
						System.out.println("dataMap.put("+rsmd.getColumnName(i)+",\""+result.getObject(i).toString());
					}				
					dataMap.put("tableName", dbname+"."+orcl_table);
					dataMap.put("rowkey", schemaRow);
					i++;
				}
				listMap.add(dataMap);
			}
	    } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
//			try {
//				con.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
		} 
		return listMap;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		System.out.println(getDate("T_ETL_JT_HPL_D_EXP_VIEW","VIEW_ID"));
	}

}
